/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.ntb.models.impl;

import com.ntb.models.*;
import com.ntb.common.DatabaseConnection;
import com.ntb.entities.LandPlansLaid;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author Thinh Hung
 */
public class LandPlansLaidDAOImpl extends DatabaseConnection implements LandPlansLaidDAO {

    public Iterator<LandPlansLaid> getList() {
        try {
            ArrayList list = new ArrayList();
            openConnection();
            ResultSet rs = getResultset("Select * From LandPlansLaid");
            while (rs.next()) {
                LandPlansLaid row = new LandPlansLaid();
                row.setPlanID(rs.getInt("PlanID"));
                row.setPlanName(rs.getString("PlanName"));

                list.add(row);
            }
            closeConnection();
            return list.iterator();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public Iterator<LandPlansLaid> getList(String landPlanName, int page) {
        try {
            ArrayList arr = new ArrayList();
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_pageLandPlansLaid(?, ?, ?)}");
            cst.setString(1, landPlanName);
            cst.setInt(2, page);
            cst.setInt(3, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            while (rs.next()) {
                LandPlansLaid row = new LandPlansLaid();
                row.setPlanID(rs.getInt("PlanID"));
                row.setPlanName(rs.getString("PlanName"));
                arr.add(row);
            }
            closeConnection();
            if (arr.size() > 0) {
                return arr.iterator();
            }
        } catch (SQLException ex) {
            Logger.getLogger(LocationsDAOImpl.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public int getPageCount(String landPlanName) {
        try {
            openConnection();
            CallableStatement cst = conn.prepareCall("{call spu_countPageLandPlansLaid(?, ?)}");
            cst.setString(1, landPlanName);
            cst.setInt(2, itemsPerPage);
            ResultSet rs = cst.executeQuery();
            int count = 0;
            if (rs.next()) {
                count = rs.getInt("Count");
            }
            closeConnection();

            return count;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return 0;
    }

    public LandPlansLaid getRow(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Select * From LandPlansLaid Where PlanID=?");
            pst.setInt(1, id);
            ResultSet rs = pst.executeQuery();
            LandPlansLaid row = new LandPlansLaid();
            if (rs.next()) {
                row.setPlanID(rs.getInt("PlanID"));
                row.setPlanName(rs.getString("PlanName"));

            }
            closeConnection();
            return row;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return null;
    }

    public boolean checkDuplicateName(String name) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Select * From LandPlansLaid Where PlanName=?");
            pst.setString(1, name);
            ResultSet rs = pst.executeQuery();
            boolean result = rs.next();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return false;
        }
    }

    public int add(LandPlansLaid object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Insert Into LandPlansLaid(PlanName) Values(?)");
            pst.setString(1, object.getPlanName());
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int update(LandPlansLaid object) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Update  LandPlansLaid Set PlanName=? Where PlanID=?");
            pst.setString(1, object.getPlanName());
            pst.setInt(2, object.getPlanID());
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int delete(int id) {
        try {
            openConnection();
            PreparedStatement pst = conn.prepareStatement("Delete LandPlansLaid Where PlanID=? " +
                    "AND ? NOT IN (SELECT PlanID FROM Lands)");
            pst.setInt(1, id);
            pst.setInt(2, id);
            int result = pst.executeUpdate();
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }

    public int deleteIn(String arrayIDs) {
        try {
            openConnection();
            String sql = "Delete Categories Where CategoryID in (" + arrayIDs + ")";
            Statement st = conn.createStatement();
            int result = st.executeUpdate(sql);
            closeConnection();
            return result;
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return -1;
    }
}
